var DATABASE_NAME = 'VMAB2013';

exports.createDb = function() {
	Ti.Database.install('VMAB2013.sqlite', DATABASE_NAME);
	//////////TRUNCATE DB//////////////////////////////
	/*
	var mydb = Ti.Database.open(DATABASE_NAME);
	mydb.execute('delete from LectureDetailDownload');
	mydb.execute('delete from LectureDownload');
	mydb.close();
	*/
};

exports.insertCategory = function(item) {
	var mydb = Ti.Database.open(DATABASE_NAME);
	mydb.execute('delete from Category where id = ?', item.id);
	var sql='insert into Category(id,title) values (?,?)'
	mydb.execute(sql, item.id, item.title);
	mydb.close();
};

exports.selectCategory = function() {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	//var rows = db.execute('select ROWID, * from todo where done = ?', _done);
	var rows = db.execute('select ROWID, * from Category order by ROWID asc');
	while (rows.isValidRow()) {
		retData.push({title:rows.fieldByName('title'), id:rows.fieldByName('id')});
		rows.next();
	}
	db.close();
	return retData;
	
};
exports.insertCategoryDetail = function(item,idCategory) {
	var mydb = Ti.Database.open(DATABASE_NAME);
	mydb.execute('delete from CategoryDetail where id = ?', item.id);
	var sql='insert into CategoryDetail(id,title,logo,idCategory) values (?,?,?,?)'
	mydb.execute(sql, item.id, item.title,item.logo,idCategory);
	mydb.close();
	//Ti.API.info ('thanh cong'+item.id+'____'+item.title+'____'+idCategory)
};
exports.selectCategoryDetail = function(idCategory) {
	//Ti.API.info ('id ne'+idCategory)
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	//var rows = db.execute('select ROWID, * from todo where done = ?', _done);
	var rows = db.execute('select ROWID, * from CategoryDetail where idCategory=? order by ROWID desc',idCategory);
	while (rows.isValidRow()) {
		retData.push({
			title:rows.fieldByName('title'), 
			id:rows.fieldByName('id'),
			logo:rows.fieldByName('logo'),
			idCategory:rows.fieldByName('idCategory'),
		});
		rows.next();
	}
	db.close();
	return retData;
	
};
exports.selectCategoryDetailAll = function() {
	//Ti.API.info ('id ne'+idCategory)
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	//var rows = db.execute('select ROWID, * from todo where done = ?', _done);
	var rows = db.execute('select ROWID, * from CategoryDetail order by ROWID desc ');
	while (rows.isValidRow()) {
		retData.push({
			title:rows.fieldByName('title'), 
			id:rows.fieldByName('id'),
			logo:rows.fieldByName('logo'),
			idCategory:rows.fieldByName('idCategory'),
		});
		rows.next();
	}
	db.close();
	return retData;
	
};
exports.insertCompanyDetail = function(item) {
	Ti.API.info ('insertCompanyDetail__'+
			item.id+'___'+
			item.category_id+'___'+
			item.logo+'___'+
			item.title+'___'+
			item.description+'___'+
			item.contact_company_name+'___'+
			item.contact_name+'___'+
			item.contact_email+'___'+
			item.contact_address+'___'+
			item.contact_phone+'___'+
			item.contact_fax+'___'+
			item.contact_web+'___'+
			item.is_showcase+'___'+
			item.is_website
	)
	var mydb = Ti.Database.open(DATABASE_NAME);
	mydb.execute('delete from CompanyDetail where id = ?', item.id);
	var sql='insert into CompanyDetail(id,category_id,logo,title,description,contact_company_name,'+
			'contact_name,contact_email,contact_address,contact_phone,contact_fax,contact_web,'+
			'is_showcase,is_website) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
			
	mydb.execute(sql, 
			item.id,
			item.category_id,
			item.logo,
			item.title,
			item.description,
			item.contact_company_name,
			item.contact_name,
			item.contact_email,
			item.contact_address,
			item.contact_phone,
			item.contact_fax,
			item.contact_web,
			item.is_showcase,
			item.is_website
	);
	mydb.close();
	
};
exports.selectCompanyDetail = function(id) {
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	//var rows = db.execute('select ROWID, * from todo where done = ?', _done);
	var rows = db.execute('select ROWID, * from CompanyDetail where id=? order by ROWID asc',id);
	while (rows.isValidRow()) {
		retData.push({
			id:rows.fieldByName('id'), 
			category_id:rows.fieldByName('category_id'),
			logo:rows.fieldByName('logo'),
			title:rows.fieldByName('title'),
			description:rows.fieldByName('description'),
			contact_company_name:rows.fieldByName('contact_company_name'),
			contact_name:rows.fieldByName('contact_name'),
			contact_email:rows.fieldByName('contact_email'),
			contact_address:rows.fieldByName('contact_address'),
			contact_phone:rows.fieldByName('contact_phone'),
			contact_fax:rows.fieldByName('contact_fax'),
			contact_web:rows.fieldByName('contact_web'),
			is_showcase:rows.fieldByName('is_showcase'),
			is_website:rows.fieldByName('is_website'),

		});
		rows.next();
	}
	db.close();
	return retData;
	
};
exports.SearchCategoryDetailAll = function(keyword) {
	//Ti.API.info ('id ne'+idCategory)
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);
	//var rows = db.execute('select ROWID, * from todo where done = ?', _done);
	var rows = db.execute("select ROWID, * from CategoryDetail where title LIKE '%"+keyword+"%' order by ROWID desc");
	while (rows.isValidRow()) {
		retData.push({
			title:rows.fieldByName('title'), 
			id:rows.fieldByName('id'),
			logo:rows.fieldByName('logo'),
			idCategory:rows.fieldByName('idCategory'),
		});
		rows.next();
	}
	db.close();
	return retData;
	
};































exports.updateItem = function(_id, _done) { 
	var mydb = Ti.Database.open(DATABASE_NAME);
	mydb.execute('update todo set done = ? where ROWID = ?', _done, _id);
	var rows = mydb.execute('select * from todo where done = ?', _done);
	mydb.close();
	return rows;
};

exports.addItem = function(_item) {
	var mydb = Ti.Database.open(DATABASE_NAME);
	mydb.execute('insert into todo values (?,?)', _item, 0);
	mydb.close();
};

exports.deleteItem = function(_id) {
	var mydb = Ti.Database.open(DATABASE_NAME);
	mydb.execute('delete from todo where ROWID = ?', _id);
	mydb.close();
};









///////////////////////////////INSERT VIDEO DOWNLOAD/////////////////////////////////////
exports.insertLectureDownload = function(item) {
	var mydb = Ti.Database.open(DATABASE_NAME);
	/*
	CREATE TABLE "LectureDownload" ("Id" INTEGER PRIMARY KEY  NOT NULL , "IdMon" INTEGER, "IdLop" INTEGER, "CategoryId" INTEGER,
	 "Name" TEXT, "Description" TEXT, "Price" FLOAT, "Published" TEXT, "Ordering" INTEGER, "Postdate" DATETIME, "UpdateDate" DATETIME, 
	 "OldId" INTEGER, "Status" INTEGER, "Images" TEXT, "Path" TEXT, "Tag" TEXT, "IdTeacher" INTEGER, "TeacherName" TEXT, "IdChuyenDe" INTEGER,
	 "ChuyenDeName" TEXT, "IdChuong" INTEGER, "ChuongName" TEXT, "TeacherImage" TEXT, "IDthaytro_Teacher" INTEGER)
	*/

	
	var sql='delete from LectureDownload where Id = ?'
	mydb.execute(sql, item.LectureId);
	
	var sql='delete from LectureDetailDownload where Id = ?'
	mydb.execute(sql, item.VideoID);
	
	//var sql='insert into LectureDownload(Id,Name,Detail,IdMon,IdLop) values (?,?,?,?,?)'
	//mydb.execute(sql, item.LectureId, item.LectureName,item.Detail,item.IdMon,item.IdLop);
	
	var sql='insert into LectureDownload(Id,Name,Detail,Price,TeacherImage) values (?,?,?,?,?)'
	mydb.execute(sql, item.LectureId, item.LectureName,item.Detail,item.Price,item.TeacherImage);
	
    var sql='insert into LectureDetailDownload(Id,LectureId,VideoName,VideoFile) values (?,?,?,?)'
	mydb.execute(sql, item.VideoID,item.LectureId,item.VideoName,item.VideoFile);
	
	
	mydb.close();
};


exports.selectLectureDownload= function() {
	
	
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select  * from LectureDownload ');
	while (rows.isValidRow()) {
		retData.push({
			Name:rows.fieldByName('Name'),
			Id:rows.fieldByName('Id'),
			Detail:rows.fieldByName('Detail'),
			IdMon:rows.fieldByName('IdMon'),
			IdLop:rows.fieldByName('IdLop'),
			Price:rows.fieldByName('Price'),
			TeacherImage:rows.fieldByName('TeacherImage')
		});
		rows.next();
	}
	db.close();
	return retData;
	
};


exports.selectLectureVideoDownload= function(LectureId) {
	
	
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select  * from LectureDetailDownload where LectureId=? order by Id',LectureId);
	while (rows.isValidRow()) {
		retData.push({

			LectureId:rows.fieldByName('LectureId'),
			Id:rows.fieldByName('Id'),
			VideoName:rows.fieldByName('VideoName'),
			VideoFile:rows.fieldByName('VideoFile')
		});
		rows.next();
	}
	db.close();
	return retData;
	
};




exports.getLop= function() {
	
	
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select  * from Lop order by [Id] desc');
	while (rows.isValidRow()) {
		retData.push({

			Id:rows.fieldByName('Id'),
			Name:rows.fieldByName('Name')
			
		});
		rows.next();
	}
	db.close();
	return retData;
	
};


exports.getMonHoc= function(IdLop) {
	
	
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select  * from Monhoc where IdLop=? order by [Order] desc',IdLop);
	while (rows.isValidRow()) {
		retData.push({

			Id:rows.fieldByName('Id'),
			Name:rows.fieldByName('Name'),
			IdLop:rows.fieldByName('IdLop'),
			Images:rows.fieldByName('Image'),
		});
		rows.next();
	}
	db.close();
	return retData;
	
};





exports.getExam= function() {
	
	
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select  * from Exam order by [Id] desc');
	while (rows.isValidRow()) {
		retData.push({
			Id:rows.fieldByName('Id'),
			Name:rows.fieldByName('Name')
		});
		rows.next();
	}
	db.close();
	return retData;
	
};


exports.getExamDetail= function(IdExam) {
	
	
	var retData = [];
	var db = Ti.Database.open(DATABASE_NAME);

	var rows = db.execute('select  * from ExamDetail where IdExam=? order by [Order] desc',IdExam);
	while (rows.isValidRow()) {
		retData.push({

			Id:rows.fieldByName('Id'),
			Name:rows.fieldByName('Name'),
			IdExam:rows.fieldByName('IdExam'),
			Images:rows.fieldByName('Image'),
			
		});
		rows.next();
	}
	db.close();
	return retData;
	
};